package com.gjzt.business.media.service;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.Date;

import net.sf.json.JSONArray;
import net.sf.json.JSONObject;

import org.apache.commons.lang.StringUtils;
import org.apache.log4j.Logger;
import org.springframework.stereotype.Service;
import org.springframework.util.Assert;

import com.eastdigit.system.service.BaseService;
import com.eastdigit.util.DateUtil;
import com.eastdigit.util.PropertiesUtil;
import com.eastdigit.util.StringUtil;

/**
 * 
 * line_stop
 * 
 * @author jun 2017-4-1
 */
@Service
public class BusPositionService extends BaseService {

    private static final String BUSGPS_DB_CONNECTION_DRIVERCLASS = PropertiesUtil
            .getProperty("busgps.db.connection.driverClass");
    private static final String BUSGPS_DB_CONNECTION_URL = PropertiesUtil.getProperty("busgps.db.connection.url");
    private static final String BUSGPS_DB_CONNECTION_USERNAME = PropertiesUtil
            .getProperty("busgps.db.connection.username");
    private static final String BUSGPS_DB_CONNECTION_PASSWORD = PropertiesUtil
            .getProperty("busgps.db.connection.password");

    public JSONObject getLastBusPosition(String vehicleNo, String lineNo) {
        Assert.hasLength(vehicleNo);
        Assert.hasLength(lineNo);

        String[] s = vehicleNo.replaceAll("[^0-9]", "#").split("#");
        String vehCode = s.length == 1 ? vehicleNo : (vehicleNo.substring(0, 1) + s[s.length - 1]);

        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        JSONObject result = null;
        JSONArray stationArr = null;
        try {
            Class.forName(BUSGPS_DB_CONNECTION_DRIVERCLASS);
            conn = DriverManager.getConnection(BUSGPS_DB_CONNECTION_URL, BUSGPS_DB_CONNECTION_USERNAME,
                    BUSGPS_DB_CONNECTION_PASSWORD);

            Date now = new Date();
            String format = "yyyyMMdd";
            DecimalFormat df = new DecimalFormat("#.00000000");
            String tableName = "";
            StringBuffer sql = null;
            int n = 0;
            while (result == null && n < 5) {
                tableName = "GPSINFO" + DateUtil.toString(DateUtil.addDay(now, (n++) * (-1)), format);
                sql = new StringBuffer("select * from (select * from " + tableName + " where VEHCODE = '" + vehCode
                        + "' ORDER BY GPSDATATIME DESC) where ROWNUM = 1 ");
                pstmt = conn.prepareStatement(sql.toString());
                rs = pstmt.executeQuery();
                while (rs.next()) {
                    result = new JSONObject();
                    String lineName = StringUtil.noNull(rs.getString("linename"));
                    String datatime = StringUtil.noNull(rs.getString("gpsdatatime"));
                    String direction = StringUtil.noNull(rs.getString("direction"));
                    String velocity = StringUtil.noNull(rs.getString("velocity"));
                    String longitude = StringUtil.noNull(rs.getString("longitude"), "0");
                    String latitude = StringUtil.noNull(rs.getString("latitude"), "0");
					String run = StringUtil.noNull(rs.getString("run"));

                    Double _longitude = Double.valueOf(longitude) / 60;
                    Double _latitude = Double.valueOf(latitude) / 60;
                    longitude = df.format(_longitude);
                    latitude = df.format(_latitude);

                    result.put("lineName", lineName);
                    result.put("gpsdatatime", datatime);
                    result.put("direction", direction);
                    result.put("velocity", velocity);
                    result.put("longitude", longitude);
                    result.put("latitude", latitude);
					result.put("run", run);

                    if (StringUtils.isNotBlank(run)) {
                        Date date = new Date();
                        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
                        String searchDate = sdf.format(date);
                        String stationSql = "SELECT STATION_NAME \"stationName\",LONGITUDE \"longitude\",LATITUDE \"latitude\"  FROM WPTG_LINE_STATION WHERE "
                                + "BACKUP_DAY = '"
                                + searchDate
                                + "' AND LINE_NAME = '"
                                + lineNo
                                + "' AND  DIRECTION = '" + velocity + "' ORDER BY DIRECTION,STATION_NO";
                        ResultSet resultSet = null;
                        PreparedStatement newPs = null;
                        try {
                            newPs = conn.prepareStatement(stationSql);
                            resultSet = newPs.executeQuery();
                            stationArr = new JSONArray();
                            while (resultSet.next()) {
                                JSONObject json = new JSONObject();
                                json.put("stationName", resultSet.getString("stationName"));
                                json.put("longitude", resultSet.getString("longitude"));
                                json.put("latitude", resultSet.getString("latitude"));
                                stationArr.add(json);
                            }
                            resultSet.close();
                            newPs.close();
                        } catch (Exception e) {
                            Logger.getLogger(this.getClass()).error(e.getMessage(), e);
                        } finally {
                            try {
                                resultSet.close();
                            } catch (SQLException e) {
                                Logger.getLogger(this.getClass()).error(e.getMessage(), e);
                            }
                            try {
                                newPs.close();
                            } catch (SQLException e) {
                                Logger.getLogger(this.getClass()).error(e.getMessage(), e);
                            }
                        }
                    }
                    result.put("stationList", stationArr);
                    break;
                }
                rs.close();
                pstmt.close();
            }
        } catch (Exception e) {
            Logger.getLogger(this.getClass()).error(e.getMessage(), e);
        } finally {
            try {
                rs.close();
            } catch (SQLException e) {
                Logger.getLogger(this.getClass()).error(e.getMessage(), e);
            }
            try {
                pstmt.close();
            } catch (SQLException e) {
                Logger.getLogger(this.getClass()).error(e.getMessage(), e);
            }
            try {
                conn.close();
            } catch (SQLException e) {
                Logger.getLogger(this.getClass()).error(e.getMessage(), e);
            }
        }
        return result;
    }

}